package gen.tools.query;

import gen.tools.Widgets;
import gen.tools.structures.CIQR1;
import gen.tools.structures.CIQR2;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class QueryChkInRecord extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public QueryChkInRecord() {
        super();
    }

	protected void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
		
		final Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/json");

		
		final ArrayList<CIQR1> c1s = new ArrayList<CIQR1>();	//一筆的查詢結果  集合
		
		String sql;
		String res = "NotOK";
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		
		try {
			conn = wid.getConn();
			
			sql = "SELECT t1.chkInMainId,t1.isDailyBuild,t1.cmFileVer,t1.svnMRver,t1.svnOLver,t1.cDT,t2.subsysId,t2.subsysDesc,t3.empId,t3.empName FROM chkinmain t1,subsyses t2,emps t3 WHERE t1.subsysId=t2.subsysId AND t1.creatorId=t3.empId ORDER BY t1.cDT DESC";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while( rs.next() ) {
				final CIQR1 c1 = new CIQR1();
				final int chkInId = rs.getInt("chkInMainId");
				final String subsysId = rs.getString("subsysId");
				final String actType = "f".equalsIgnoreCase( rs.getString("isDailyBuild") )?"OL":"MR";
				
				c1.setChkInId(chkInId);	//繳交記錄ID
				c1.setMR( "t".equalsIgnoreCase( rs.getString("isDailyBuild") ) );	//是否為每日建置
				c1.setSvnCMver( rs.getInt("cmFileVer") );	//SVN CM 原始碼區的版次
				c1.setSvnAPmrVer( rs.getInt("svnMRver") );	//SVN AP MR 原始碼區的版次
				c1.setSvnAPolVer( rs.getInt("svnOLver") );	//SVN AP OL 原始碼區的版次
				c1.setcDT( wid.changeTimeFormatUI( rs.getTimestamp("cDT") ) );
				c1.setSubsysId( subsysId );
				c1.setSubsysDesc( rs.getString("subsysDesc") );
				c1.setCreatorId( rs.getString("empId") );
				c1.setCreatorName( rs.getString("empName") );
				
				sql= "SELECT modId FROM chkinmod WHERE chkInMainId=?";
				ps2 = conn.prepareStatement(sql);
				ps2.setInt(1, c1.getChkInId() );
				rs2 = ps2.executeQuery();
				
				final ArrayList<CIQR2> c2s = new ArrayList<CIQR2>(); 
				int count=0;
				while( rs2.next() ) {
					count++;
					final String modId = rs2.getString("modId");
					final String jobName = actType+"_"+subsysId+"_"+modId;
					final CIQR2 c2 = new CIQR2();
					ps3 = conn.prepareStatement("SELECT COUNT(isMR) FROM chkinmoddetail WHERE chkInMainId=? AND isMR=? AND modId=?");
					ps3.setInt(1, c1.getChkInId() );
					ps3.setString(3, modId);
					ps3.setString(2, "t");
					rs3 = ps3.executeQuery();
					rs3.next();
					int mrNum = rs3.getInt(1);	//取得來自 SVN AP MR 原始碼區的程式數
					ps3.setString(2, "f");
					rs3 = ps3.executeQuery();
					rs3.next();
					int olNum = rs3.getInt(1);
					
					String depFile ="";
					int depFileVer =0;
					int statusId = 0;
					String statusDesc = "";
					
					sql = "SELECT t2.chkInStatusDesc,t2.chkInStatusId FROM chkinmain t1,chkinstatus t2 WHERE t1.chkInStatusId=t2.chkInStatusId AND t1.chkInMainId=?";
					ps3 = conn.prepareStatement(sql);
					ps3.setInt(1, chkInId );
					rs3 = ps3.executeQuery();
					rs3.next();
					
					final int chkInStatus = rs3.getInt("chkInStatusId");
					statusId = rs3.getInt("chkInStatusId");
					statusDesc = rs3.getString("chkInStatusDesc");
					if ( chkInStatus == 5 ) {
						System.out.println("繳交出現問題的單號："+chkInId+","+jobName);
						sql = "SELECT t1.depFileName,t1.deployFileVer,t1.beStatusId,t2.beStatusDesc FROM bemain t1, bestatus t2 WHERE t1.beStatusId=t2.beStatusId AND t1.chkInMainId=? AND t1.beStatusId<>1 AND t1.rootJobName=?";
						ps3 = conn.prepareStatement(sql);
						ps3.setInt(1, chkInId );
						ps3.setString(2, jobName);
						rs3 = ps3.executeQuery();
						
						if ( !rs3.next() ) {
							sql = "SELECT rootJobName FROM bemain WHERE chkInMainId=? AND modId=?";
							ps3 = conn.prepareStatement(sql);
							ps3.setInt(1, chkInId );
							ps3.setString(2, jobName);
							rs3 = ps3.executeQuery();
							rs3.next();
							final String tmpStr = rs3.getString("rootJobName");
							sql = "SELECT t1.depFileName,t1.deployFileVer,t1.beStatusId,t2.beStatusDesc FROM bemain t1, bestatus t2 WHERE t1.beStatusId=t2.beStatusId AND t1.chkInMainId=? AND t1.beStatusId<>1 AND t1.rootJobName=?";
							ps3 = conn.prepareStatement(sql);
							ps3.setInt(1, c1.getChkInId() );
							ps3.setString(2, tmpStr);
							rs3 = ps3.executeQuery();
							rs3.next();
						} 
						depFile = rs3.getString("depFileName");
						depFileVer = rs3.getInt("deployFileVer");
						statusId = rs3.getInt("beStatusId");
						statusDesc = rs3.getString("beStatusDesc");
					} 
					
					if ( count==1 ) {
						c1.setModId(modId);
						c1.setMrCount( mrNum );
						c1.setOlCount( olNum );
						c1.setDepFile( depFile );
						c1.setSvnCMcapVer( depFileVer );
						c1.setStatusId(statusId);
						c1.setStatusDesc(statusDesc);
					} else {
						c2.setModId(modId);
						c2.setMrCount( mrNum );		
						c2.setOlCount( olNum );
						c2.setDepFile( depFile );
						c2.setSvnCMcapVer(depFileVer );
						c2.setStatusId(statusId);
						c2.setStatusDesc(statusDesc);
						//到目前為止，到畫面第二層的資訊都有了
						c2s.add(c2);
					}
				}
				c1.setC2s(c2s);
				c1.setRsC1(count);
				c1s.add(c1);
			}
		} catch(Exception e) {
			wid.errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( rs2 !=null ) {rs2.close();}
				if ( rs3 !=null ) {rs3.close();}
				if ( ps !=null ) {ps.close();}
				if ( ps3 !=null ) {ps3.close();}
				if ( ps2 !=null ) {ps2.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		res = new Gson().toJson(c1s);
		
		response.getWriter().write(res);

	}

}
